require 'mysql2'
require 'spreadsheet'
require 'date'

# 数据库配置 - 请替换为你的实际配置
DB_CONFIG = { host: ENV['RDS_AGENT'], username: 'psi_root', port: 1403, password: ENV['PSI_PASSWORD'], database: 'tutem_mall' }

# 定义时间范围常量
QUARTERS = [
  { name: '2024Q3', start: '2024-07-01 00:00:00', end: '2024-10-01 00:00:00' },
  { name: '2024Q4', start: '2024-10-01 00:00:00', end: '2025-01-01 00:00:00' },
  { name: '2025Q1', start: '2025-01-01 00:00:00', end: '2025-04-01 00:00:00' },
  { name: '2025Q2', start: '2025-04-01 00:00:00', end: '2025-07-01 00:00:00' }
]

# 生成SQL查询
def generate_sql
  # 构建季度条件表达式
  sales_columns = QUARTERS.map do |q|
    "SUM(IF(o.pay_time >= UNIX_TIMESTAMP('#{q[:start]}') "\
    "AND o.pay_time < UNIX_TIMESTAMP('#{q[:end]}'), "\
    "o.pay_price - o.refund_price, 0)) AS `#{q[:name]}实销额`"
  end

  patient_columns = QUARTERS.map do |q|
    "SUM(IF(o.pay_time >= UNIX_TIMESTAMP('#{q[:start]}') "\
    "AND o.pay_time < UNIX_TIMESTAMP('#{q[:end]}') "\
    "AND o.tags LIKE '%NPO%', 1, 0)) AS `#{q[:name]}新患数`"
  end

  columns = [
    "c.cid",
    "c.cname",
    "c.rep",
    *sales_columns,
    *patient_columns,
    "SUM(o.pay_price - o.refund_price) AS `FY2024-SALES`",
    "SUM(IF(o.tags LIKE '%NPO%', 1, 0)) AS `FY2024-NPO`"
  ]

  <<-SQL
    SELECT #{columns.join(",\n       ")}
    FROM (
      SELECT 
        uid AS cid, 
        real_name AS cname, 
        rep_code AS rep 
      FROM eb_user 
      WHERE rep_code IN ('DXF','PRB','PJQ','LJ1','CST','LXH')
    ) c
    LEFT JOIN eb_user u ON u.spread_uid = c.cid
    LEFT JOIN eb_store_order o 
      ON o.uid = u.uid 
      AND o.paid = 1 
      AND o.pay_type = 'weixin'
      AND o.pay_time >= UNIX_TIMESTAMP('#{QUARTERS.first[:start]}')
      AND o.pay_time < UNIX_TIMESTAMP('#{QUARTERS.last[:end]}')
    GROUP BY c.cid, c.cname, c.rep
    ORDER BY 
      c.rep DESC,
      `FY2024-SALES` DESC,
      `FY2024-NPO` DESC
  SQL
end

begin
  # 创建数据库客户端
  client = Mysql2::Client.new(DB_CONFIG)
  
  # 执行SQL查询
  puts "正在执行数据库查询..."
  sql = generate_sql
  results = client.query(sql)
  puts "获取到 #{results.count} 条记录"
  
  # 创建Excel工作簿
  book = Spreadsheet::Workbook.new
  sheet = book.create_worksheet(name: '渠道销售数据')
  
  # 设置标题样式
  header_format = Spreadsheet::Format.new(
    weight: :bold,
    size: 12,
    pattern_fg_color: :gray,
    pattern: 1
  )
  
  # 写入表头
  headers = [
    '渠道ID', '渠道名称', '代表代码',
    '2024Q3实销额', '2024Q3新患数',
    '2024Q4实销额', '2024Q4新患数',
    '2025Q1实销额', '2025Q1新患数',
    '2025Q2实销额', '2025Q2新患数',
    '4个季度实销总额', '4个季度新患数'
  ]
  
  sheet.row(0).concat(headers)
  sheet.row(0).default_format = header_format
  
  # 写入数据
  puts "正在写入Excel数据..."
  results.each_with_index do |row, i|
    data_row = [
      row['cid'], row['cname'], row['rep'],
      row['2024Q3实销额'], row['2024Q3新患数'],
      row['2024Q4实销额'], row['2024Q4新患数'],
      row['2025Q1实销额'], row['2025Q1新患数'],
      row['2025Q2实销额'], row['2025Q2新患数'],
      row['4个季度实销总额'], row['4个季度新患数']
    ]
    
    sheet.row(i + 1).concat(data_row)
    
    # 格式化金额为两位小数
    [3, 5, 7, 9, 11].each do |col|
      sheet.row(i + 1).set_format(col, Spreadsheet::Format.new(number_format: '0.00'))
    end
  end
  
  # 设置列宽
  widths = [10, 20, 10] + [15, 10] * 4 + [18, 15]
  widths.each_with_index do |width, col_idx|
    sheet.column(col_idx).width = width
  end
  
  # 保存文件
  filename = "渠道销售报告_#{Time.now.strftime('%Y%m%d_%H%M')}.xls"
  book.write(filename)
  puts "文件已保存: #{File.expand_path(filename)}"

rescue Mysql2::Error => e
  puts "数据库错误: #{e.message}"
rescue => e
  puts "发生错误: #{e.message}\n#{e.backtrace.join("\n")}"
ensure
  client&.close
end
